package db;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import model.City;
import model.Country;
import util.Number;

public class DBCity implements IFCity
{
	private Connection con;

	public DBCity()
		{
			con = DBConnection.getInstance().getDBcon();
		}

	@Override
	public ArrayList<City> getCitiesByAllInfo(String search) throws SQLException {
		boolean number = Number.isNumber(search);

		return searchCity("city.city_name LIKE ? AND city.city_country_id LIKE ?" + (number ? "OR city.city_id = ?" : "") + "" , (number ? 3 : 2),  search);
	}

	@Override
	public ArrayList<City> getCityByName(String name) throws SQLException {

		return searchCity("city.city_name LIKE ?", 1, name);
	}

	@Override
	public ArrayList<City> getCitiesByCountryName(String name) throws SQLException {

		return searchCity("country.country_name LIKE ?", 1,name);
	}

	@Override
	public int createCity(City city) throws Exception {
		int rc = -1;

		ResultSet results;

		String query_check = "SELECT city_name FROM city WHERE city_name = ?;";

		if(DBConnection.doDebug())
			{
				DBConnection.printDebug("createCity", query_check);
			} // end of if

		try
			{
				PreparedStatement ps = con.prepareStatement(query_check);

				ps.setString(1,  city.getName());

				results = ps.executeQuery();

				if(!results.next())
					{
						String query = "INSERT INTO city(city_name, city_country_id)" +
								" VALUES( ?, ?);";

						if(DBConnection.doDebug())
							DBConnection.printDebug("createCity", query);

						try
							{
								ps = con.prepareStatement(query);

								ps.setString(1, city.getName());
								ps.setInt(2, city.getCountry().getCountryID());
								
								rc = ps.executeUpdate();
							} // end of try
						catch(Exception e)
							{
								if(DBConnection.doDebug())
									{
										DBConnection.printDebug("Error in createCity", query);
										e.printStackTrace();
									} // end of if
							} // end of catch
					} // end of if
				else
					{
						rc = -1;
					} // end of else
				ps.close();
			}
		catch(Exception e)
			{
				if(DBConnection.doDebug())
					{
						DBConnection.printDebug("createCity", query_check);
					} // end of if
			}
		return rc;
	}

	@Override
	public int updateCity(City city) throws Exception {
		int rc = -1;

		String query = "UPDATE city SET city_name = ?, city_country_id = ? WHERE city_id = ?;";

		if(DBConnection.doDebug())
			DBConnection.printDebug("updateCity", query);

		try
			{
				PreparedStatement ps = con.prepareStatement(query);

				ps.setString(1, city.getName());
				ps.setInt(2, city.getCountry().getCountryID());
				ps.setInt(3, city.getCityID());

				rc = ps.executeUpdate();

				ps.close();
			} // end of try
		catch(Exception e)
			{
				if(DBConnection.doDebug())
					{
						DBConnection.printDebug("updateCity Error", query);
						e.printStackTrace();
					} // end of if
			} // end of catch

		return rc;
	}

	private City buildCity(ResultSet results) throws SQLException
	{
		City city = new City();

		try
			{
				city.setCityID(results.getInt("city_id"));
				city.setName(results.getString("city_name"));
			} // end of try
		catch( Exception e)
			{
				System.out.println("could not build the city, error : " + e);

			} // end of catch

		return city;
	}

	private Country buildCountry(ResultSet results) throws SQLException
	{
		Country country = new Country();

		try
			{
				country.setCountryID(results.getInt("city_country_id"));
				country.setName(results.getString("country_name"));
				country.setTld(results.getString("country_tld"));
			} // end of try
		catch(Exception e)
			{
				System.out.println("could not build the country, error : " + e);
			} // end of city

		return country;
	}



	public ArrayList<City> searchCity(String clause, int num, String search) throws SQLException
	{
		ArrayList<City> cityList = new ArrayList<City>();

		ResultSet results;

		City city = new City();
		Country country = new Country();


		String query = "SELECT city.*, country.country_name, country.country_tld FROM city LEFT JOIN country ON city.city_country_id = country.country_id WHERE " + ((clause.length() > 0 ) ? ("" + clause) : "") +
				" ORDER BY city.city_name ASC;";

		if(DBConnection.doDebug())
			DBConnection.printDebug("searchCity", query);

		try
			{
				PreparedStatement ps = con.prepareStatement(query);

				if(clause.length() > 0)
					{
						ps.setString(1, (num == -1 ? search : "%" + search + "%"));

						if( num > 1)
							{
								for(int i = 1; i <= num ; i++)
									{
										ps.setString(i, (i == 3 ? search : "%" + search + "%"));
									} // end of for
							} // end of if
					} // end of if
				results = ps.executeQuery();
				while(results.next())
					{
						city = buildCity(results);
						country = buildCountry(results);

						city.setCountry(country);

						cityList.add(city);

					} // end of while

				ps.close();
			} // end of try
		catch(Exception e)
			{
				if(DBConnection.doDebug())
					{
						DBConnection.printDebug("searchCity", query);
						System.out.println("error query exception - searchCity - SELECT: " + e);
						e.printStackTrace();
					} // end of if
			} // end of catch

		return cityList;
	}

	@Override
	public City  getOneCity(String column, String value, String countryName) throws SQLException
		{
			ResultSet results;

			City city = new City();
			Country country = new Country();
			String columner = null;
			
			switch (column) 
			{
				case "id" : columner = "city.city_id = ?"; break;
				
				case "name" : columner = "city.city_name = ? AND country.country_name = ?;"; break;
			}
			

			String query = "SELECT city.*, country.country_name, country.country_tld FROM city LEFT JOIN country ON city.city_country_id = country.country_id WHERE " + columner ;

			if(DBConnection.doDebug())
				DBConnection.printDebug("searchCity", query);

			try
				{
					PreparedStatement ps = con.prepareStatement(query);

							ps.setString(1, value);
							
							switch(column) 
							{
								case "name" : ps.setString(2, countryName); break;
							}
							

					results = ps.executeQuery();
					if(results.next())
						{
							city = buildCity(results);
							country = buildCountry(results);

							city.setCountry(country);
						} // end of if

					ps.close();
				} // end of try
			catch(Exception e)
				{
					if(DBConnection.doDebug())
						{
							DBConnection.printDebug("getOneCityByName", query);
							System.out.println("error query exception - getOneCityByName - SELECT: " + e);
							e.printStackTrace();
						} // end of if
				} // end of catch

			return city;
		}

	@Override
	public ArrayList<City> getAllCities() throws SQLException
		{
			ArrayList<City> cityList = new ArrayList<City>();

			ResultSet results;

			City city = new City();
			Country country = new Country();


			String query = "SELECT city.*, country.country_name, country.country_tld FROM city LEFT JOIN country ON city.city_country_id = country.country_id ORDER BY city.city_name ASC;";

			if(DBConnection.doDebug())
				DBConnection.printDebug("getAllCities", query);

			try
				{
					PreparedStatement ps = con.prepareStatement(query);

					results = ps.executeQuery();
					while(results.next())
						{
							city = buildCity(results);
							country = buildCountry(results);

							city.setCountry(country);

							cityList.add(city);

						} // end of while

					ps.close();
				} // end of try
			catch(Exception e)
				{
					if(DBConnection.doDebug())
						{
							DBConnection.printDebug("getAllCities", query);
							System.out.println("error query exception - getAllCities - SELECT: " + e);
							e.printStackTrace();
						} // end of if
				} // end of catch

			return cityList;
		}

}